GIS

• Geographical Information Science (GIS) is the science of analysis, storage, visualisation and management of geographic/spatial data.

• Geographical Information System (GIS) is a system designed to analyse, store, visualise and manage geographic/spatial data.

Spatial data is characterised by coordinates, which allow to identify where an object is on earth.

Spatial objects can be static or dynamic

Static

Remain at the same location at the relevant time scale

Dynamic

Change location at the relevant time scale

GIS and Animal Movement

Spatial data and good knowledge of GIS is of fundamental importance to study animal movement:

• How?
• Why?
• When?
• Where?

Overview

• Spatial data types
• Environmental datasets
  
• Spatial Operations
• Geographic Reference Systems and Projections
• Software for spatial analysis
• Spatial Database
  • Hands-on

Vector

Stores spatial information as vector-based object types.

Spatial objects in Movement Ecology

• Points
  • GPS locations
  • Buildings, feeding sites
• Lines
  • Trajectory
  • Roads, rivers
• Polygons
  • Home range
  • Land cover, Habitat

##     animals_original_name longitude latitude    acquisition_time
## 1                 Daniela  11.04413 46.01096 2005-10-18 22:00:54
## 2                 Daniela  11.04454 46.01178 2005-10-19 02:01:23
## 3                 Daniela  11.04515 46.00793 2005-10-19 06:02:22
## 4                 Daniela  11.04567 46.00600 2005-10-19 10:03:08
## 5                 Daniela  11.04699 46.00535 2005-10-19 18:03:10
## 6                 Daniela  11.04290 46.01031 2005-10-19 22:01:03
## 7                 Daniela  11.04259 46.01169 2005-10-20 02:00:48
## 8                 Daniela  11.04110 46.01051 2005-10-20 06:00:48
## 9                 Daniela  11.04721 46.00575 2005-10-20 14:02:58
## 10                Daniela  11.04286 46.01015 2005-10-20 22:00:53
## 11                Daniela  11.04172 46.01051 2005-10-21 02:00:48
## 12                Daniela  11.04089 46.01028 2005-10-21 06:00:53
## 13                Daniela  11.04429 46.00669 2005-10-21 10:01:42
## 14                Daniela  11.04622 46.00684 2005-10-21 18:01:16
## 15                Daniela  11.03812 46.00939 2005-10-21 22:01:23
## 16                Daniela  11.03956 46.01017 2005-10-22 02:00:55
## 17                Daniela  11.03871 46.00939 2005-10-22 06:00:47
## 18                Daniela  11.03764 46.00189 2005-10-22 10:02:22
## 19                Daniela  11.04306 46.00600 2005-10-22 14:01:20
## 20                Daniela  11.04083 46.01112 2005-10-22 22:01:11
## 21                Daniela  11.04089 46.01101 2005-10-23 02:00:54
## 22                Daniela  11.04083 46.01109 2005-10-23 06:00:55
## 23                Daniela  11.04620 46.00823 2005-10-23 10:03:03
## 24                Daniela  11.05925 45.99696 2005-10-23 14:01:48
## 25                Daniela  11.05910 45.99684 2005-10-23 18:01:38
## 26                Daniela  11.05476 46.00491 2005-10-23 22:01:53
## 27                Daniela  11.04572 46.00913 2005-10-24 02:00:48
## 28                Daniela  11.04085 46.01138 2005-10-24 06:00:42
## 29                Daniela  11.03731 46.00700 2005-10-24 10:02:48
## 30                Daniela  11.03688 46.00734 2005-10-24 14:01:15
## 31                Daniela  11.03690 46.00729 2005-10-24 18:01:24
## 32                Daniela  11.03862 46.00904 2005-10-24 22:01:51
## 33                Daniela  11.03950 46.01011 2005-10-25 02:00:54
## 34                Daniela  11.03933 46.01009 2005-10-25 06:00:55
## 35                Daniela  11.03675 46.00712 2005-10-25 10:02:25
## 36                Daniela  11.03596 46.00523 2005-10-25 14:02:32
## 37                Daniela  11.03614 46.00562 2005-10-25 18:02:39
## 38                Daniela  11.03790 46.00740 2005-10-25 22:01:52
## 39                Daniela  11.03897 46.00896 2005-10-26 02:00:48
## 40                Daniela  11.03778 46.00848 2005-10-26 06:00:54
## 41                Daniela  11.03689 46.00722 2005-10-26 14:01:23
## 42                Daniela  11.03612 46.00538 2005-10-26 18:00:54
## 43                Daniela  11.03838 46.00916 2005-10-26 22:00:53
## 44                Daniela  11.03866 46.00977 2005-10-27 02:00:48
## 45                Daniela  11.03866 46.01003 2005-10-27 06:00:47
## 46                Daniela  11.03327 46.00581 2005-10-27 14:01:54
## 47                Daniela  11.03729 46.00691 2005-10-27 22:01:27
## 48                Daniela  11.03935 46.00991 2005-10-28 02:01:18
## 49                Daniela  11.04079 46.01248 2005-10-28 06:00:54
## 50                Daniela  11.03627 46.00735 2005-10-28 10:01:25
## 51                Daniela  11.03673 46.00724 2005-10-28 14:00:55
## 52                Daniela  11.03694 46.00727 2005-10-28 18:02:47
## 53                Daniela  11.04132 46.01220 2005-10-28 22:00:54
## 54                Daniela  11.04105 46.01254 2005-10-29 02:00:53
## 55                Daniela  11.04102 46.01224 2005-10-29 06:01:17
## 56                Daniela  11.03613 46.00741 2005-10-29 10:02:51
## 57                Daniela  11.03484 46.00563 2005-10-29 14:02:25
## 58                Daniela  11.03246 46.00537 2005-10-29 18:03:03
## 59                Daniela  11.03827 46.00797 2005-10-29 22:01:18
## 60                Daniela  11.03924 46.00959 2005-10-30 02:01:11
## 61                Daniela  11.03719 46.00733 2005-10-30 05:00:56
## 62                Daniela  11.03591 46.00525 2005-10-30 09:02:57
## 63                Daniela  11.03599 46.00521 2005-10-30 13:00:55
## 64                Daniela  11.03595 46.00522 2005-10-30 17:01:39
## 65                Daniela  11.03971 46.00993 2005-10-30 21:01:11
## 66                Daniela  11.04021 46.00993 2005-10-31 01:01:24
## 67                Daniela  11.04005 46.01046 2005-10-31 05:00:54
## 68                Daniela  11.03701 46.00733 2005-10-31 09:02:21
## 69                Daniela  11.03912 46.00931 2005-10-31 21:00:55
## 70                Daniela  11.04018 46.01047 2005-11-01 01:00:55
## 71                Daniela  11.04094 46.01113 2005-11-01 05:00:54
## 72                Daniela  11.03698 46.00736 2005-11-01 09:03:07
## 73                Daniela  11.03595 46.00516 2005-11-01 13:02:04
## 74                Daniela  11.03665 46.00575 2005-11-01 17:03:02
## 75                Daniela  11.03847 46.00901 2005-11-01 21:00:54
## 76                Daniela  11.04084 46.01100 2005-11-02 01:00:48
## 77                Daniela  11.04141 46.01104 2005-11-02 05:00:54
## 78                Daniela  11.03664 46.00605 2005-11-02 09:01:11
## 79                Daniela  11.03650 46.00569 2005-11-02 13:02:52
## 80                Daniela  11.03607 46.00512 2005-11-02 17:01:54
## 81                Daniela  11.04038 46.00938 2005-11-02 21:01:18
## 82                Daniela  11.04070 46.01015 2005-11-03 01:00:47
## 83                Daniela  11.04139 46.01022 2005-11-03 05:01:54
## 84                Daniela  11.03452 46.00446 2005-11-03 09:03:00
## 85                Daniela  11.03977 46.00976 2005-11-03 21:00:54
## 86                Daniela  11.04063 46.01080 2005-11-04 01:00:54
## 87                Daniela  11.04143 46.01156 2005-11-04 05:00:54
## 88                Daniela  11.03670 46.00559 2005-11-04 09:01:52
## 89                Daniela  11.03625 46.00451 2005-11-04 13:01:52
## 90                Daniela  11.03668 46.00585 2005-11-04 17:00:54
## 91                Daniela  11.04024 46.00971 2005-11-04 21:01:23
## 92                Daniela  11.04109 46.01121 2005-11-05 01:00:48
## 93                Daniela  11.04113 46.01217 2005-11-05 05:00:56
## 94                Daniela  11.03629 46.00505 2005-11-05 17:00:54
## 95                Daniela  11.03712 46.00709 2005-11-05 21:00:55
## 96                Daniela  11.03754 46.00817 2005-11-06 01:00:53
## 97                Daniela  11.03746 46.00706 2005-11-06 05:00:54
## 98                Daniela  11.03505 46.00596 2005-11-06 09:01:46
## 99                Daniela  11.03528 46.00619 2005-11-06 13:02:30
## 100               Daniela  11.03671 46.00665 2005-11-06 17:01:13

see also here

Visualising Trajectories

Home Range

• The home range is the area an animal crosses during its normal feeding and reproductive behaviours, excluding occasional outer excursions (Burt, 1943).
• The home range is the animal cognitive map of the area where it lives (Powell, 2000).

Home Range Estimation methods

Spatial explicit:

• MCP - Minimum Convex Polygon
• Kernel UD - Kernel Utilisation Distribution
• LoCoH - Local Convex Hull

Spatial and Temporal explicit:

• t-LoCoH - Temporal Local Convex Hull
• BBMM - Brownian Bridge Movement Model

MCP - Minimum Convex Polygon

The smallest polygon in which no internal angle exceeds 180 degrees and which contains all sites (Burgman & Fox, 2003).

Getz & Wilmers, 2004

Horne et al. 2007.

Time explicit version of Kernel Utilisation Distribution

Raster

Stores spatial information in a grid or matrix of cells.

How detailed the information is depends on the resolution, i.e. the cell size of each grid cell.

Environmental datasets:

• Copernicus
• Google Earth Engine
• Open Street Maps

library(osmdata)

q <- opq(bbox = coords_bbox) %>% add_osm_feature(key = 'building')
cway_sev <- osmdata_sp(q)

Spatial Operations and definitions

• Overlay tools Overview
• Extract tools Overview
• Proximity tools Overview

Centroid

centroid_mcp90 <- st_centroid(mcp90)
centroid_traj <- st_centroid(trajectories)

Centroid

Filter/Subset

Example: Extract locations in January from Agostino.

agostino_2006_jan <- subset(locations, 
                            animals_original_name == 'Agostino' & 
                            mn == 1 & 
                            yr == 2006) 

Filter/Subset

Extent

Example: Get extent for locations in January from Agostino

# filter
agostino_2006_jan <- subset(locations, 
                            animals_original_name == 'Agostino' & 
                            mn == 1 & 
                            yr == 2006) 
# get extent
extent_agostino_jan <- extent(agostino_2006_jan)

Extent

Crop

Returns a geographic subset of an object as specified by an Extent object.

Example: Get forest pixels for the extent of the GPS location of Agostino in January.

Crop

# filter
agostino_2006_jan <- subset(locations, 
                            animals_original_name == 'Agostino' & 
                            mn == 1 & 
                            yr == 2006) 
# get extent 
extent_agostino_jan <- extent(agostino_2006_jan)
# crop 
forest_crop_agostino_jan <- crop(forest_raster,
                                 extent_agostino_jan)

Crop

Mask

Create a new Raster object that has the same values as the raster, except for the cells that are NA in a mask

Example: Get forest cover in the MCP of Agostino in January?

Mask

# filter 
agostino_2006_jan <- subset(locations, 
                            animals_original_name == 'Agostino' & 
                            mn == 1 & 
                            yr == 2006) 
# convert spatial format to sp
agostino_2006_jan <- as(agostino_2006_jan[,c('animals_original_name','geom')],'Spatial')
# get extent 
extent_agostino_jan <- extent(agostino_2006_jan)
# crop 
forest_crop_agostino_jan <- crop(forest_raster,extent_agostino_jan)
# calculate mcp 
agostino_2006_jan_mcp <- mcp(agostino_2006_jan,percent=100)
# mask raster cells outside the mcp 
forest_mask_agostino_jan <- mask(forest_crop_agostino_jan,
                                 agostino_2006_jan_mcp)

Mask

Extract

Extract values from a Raster object at the locations of other spatial data.

Extract - points

Example: extract the forest percentage for the gps locations of Agostino in January

extract(forest_raster, agostino_2006_jan)

##  [1]  96  97  98  98  97 100  97  98 100  96  97  98  87 100  97  98  98
## [18]  92  91  91  92  98 100  97  98  97  98  98 100  99  99  88  89 100
## [35]  88  71  91  96  97  97  97  98  98  98 100  82  94  98  52  91  78
## [52] 100 100  96  98  94  87  97 100  94 100  98  99  97  82 100 100  88
## [69] 100  89 100  99  97  99 100  66 100  99  89  97  98  97  98  98  98
## [86] 100 100  98  97 100 100  98  98  97

Extract - Points

Extract - Polygons

Example: extract the forest percentage for the MCP of Agostino calculated for January

extract(forest_raster, agostino_2006_jan_mcp)

## [[1]]
##   [1]  91  77  92  98 100  93  99  99 100  99 100  71  89  97  99 100  97
##  [18]  98  99 100  90  89  94  99 100 100  75  90  98  99  99  87  88  98
##  [35]  99  99 100 100 100  99  99  98  98  91  89  89 100  98  99 100 100
##  [52] 100 100  99  99  93  87  86  89  93  98  95  95 100  99 100 100 100
##  [69]  99  95  87  90  99  91  94  97  97  99  98  98 100 100 100  99  98
##  [86]  99  99  98  99  52  71  97  97  97  96 100 100 100 100 100 100 100
## [103]  99  92  94   0  48  93  99 100  95 100 100  99  98 100 100 100 100
## [120]  98  97  98  87  66  89  98 100  99  99 100 100 100  98  98  98  98
## [137] 100  99 100  98  94  88  88 100  99  99  99 100  98  98  98  95  93
## [154]  97  99  99  99  97  99  88  99 100 100  99  99 100  98  98  98  98
## [171]  98 100  99  99  98  98 100  68  96 100 100  99 100  99  99  98  97
## [188]  96  98 100 100 100 100 100  98  98  66 100  92  90  99  99  98 100
## [205] 100  98  98  98 100  99  98 100 100  98  72  68 100  94  89  98 100
## [222] 100  99 100 100 100  98  97  97  97 100 100  94  65  95  87  97  98
## [239]  99 100 100 100  99  98  98  95  93  94 100  91  78  75  91  86  98
## [256]  94  98  99  99 100  98  96  88  95  99  79  80  81  68  89  89 100
## [273] 100 100 100  99 100  91  99  66  71  67  73  99  99  99  75  63  75
## [290]  72  76  99  99  99  74  63  85  99 100 100  99 100

Extract - Polygons

Forest cover percentage in different months

Buffer

# Subset Agostino 
agostino_traj <- subset(trajectories, 
                        animals_original_name == 'Agostino') 
# make a buffer of 200 m around the trajectory 
buffer_traj200m <- st_buffer(agostino_traj, 200)
buffer_traj400m <- st_buffer(agostino_traj, 400)

Buffer

Generate random points

# generate random points
random_points <- spsample(x=as(buffer_traj400m,'Spatial'), n=300,type='random')

Generate random points

Erase

Erase

# erase inner buffer
erase_part <- erase(buffer_400m, buffer_200m)

intersection of layers

• Which/How many houses are close to the trajectory (0-200m)?
• Which/How many houses are from the trajectory (200-400m)?

buildings_3035 <- st_transform(buildings, crs=3035)
# houses between 200 and 400 m away from the trajectory
houses_further_from_trajectory <- st_intersection(buildings_3035, 
                                                  buffer_200_400m)  
# houses between 0 and 200 m away from the trajectory
houses_near_trajectory <- st_intersection(buildings_3035, 
                                          buffer_0_200m)   
houses_near_trajectory

## Geometry set for 45 features 
## geometry type:  POINT
## dimension:      XY
## bbox:           xmin: 4399212 ymin: 2542156 xmax: 4404238 ymax: 2549849
## epsg (SRID):    3035
## proj4string:    +proj=laea +lat_0=52 +lon_0=10 +x_0=4321000 +y_0=3210000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs
## First 5 geometries:

intersection of layers

intersection of layers

• Density of houses?

# area of buffer areas
area_200_400_buffer <- as.numeric(st_area(buffer_200_400m))/100000
area_0_200_buffer <- as.numeric(st_area(buffer_0_200m))/100000

# density of houses at 200 and 400 m 
# Devide the number of houses by the area
# 0-200 m
density_houses_0_200 <- round(length(houses_near_trajectory)/area_0_200_buffer,4)
# 200-400 m 
density_houses_200_400 <- round(length(houses_further_from_trajectory)/area_200_400_buffer,4)

## [1] "0-200 m:0.4299 houses per km2"

## [1] "200-400 m:0.6829 houses per km2"

Dissolve

Some common projections:

• (Transverse) Mercator projection
• Robinson projection
• Lambert Conformal Conic

Map distortion: distortion of features on the earth’s surface

• shape
• size
• distance
• angle

(Transverse) Mercator projection: overestimation of surface towards poles

Interesting links to explore map projections and map distortion.

• The True Size: How big is your country?
• Map characteristics
• Compare projections

Coordinates

• Coordinates: numbers indicating position on the earth surface.
  • X and Y (in meters)
  • Longitude and Latitude (in degrees)

Geographical reference systems

To assign coordinates to a location on the earth’s surface you need a Geographic Reference System:

• Ellipsoid: shape and size.
• Datum: account for local variation in elevation.
• zero X and Y reference axis: to assign coordinates to a location.

Datum

Every country (or group of countries) has it’s own vertical (height) datum (i.e., Mean Sea Level).

Zero X and Y reference axis

GPS and googlemaps reference axis:

• Longitude the measurement of the angle from the prime meridian at Greenwich, England, to the center of the earth to the location.
• Latitude the measurement of the angle from the equator to the center of the earth to the location.

Geographical reference systems

• World Geodetic System
  • (WGS84, EPSG:4326)
• Projected coordinate system for Europe
  • (ETRS89, EPSG:3035)
• Universal Transverse Mercator coordinate system
  • (UTM)

Two Types of Geographical reference systems

• global or spherical reference systems
• projected reference systems

global or spherical reference systems

• World Geodetic System (WGS84, EPSG:4326)
  • geodetic datum & ellipsoid: WGS84
  • zero reference axes: the prime meridian at Greenwich (longitude) and the equator (latitude).
  • units: degrees (longitude-latitude)

Example: I work here: longitude = 11°08’10.7“E, latitude = 46°11’30.5”N)

Projected reference systems

• Universal Transverse Mercator coordinate system (UTM)
  • ellipsoid: WGS84
  • datum: depend from the grid cell (e.g.: ED50, NAD83)
  • zero reference axes: depend from the grid cell - a central meridian for each six-degree wide UTM zone
  • units: meters

In UTM the earth is devided into a grid, where each grid cell is projected using a standard set of map projections

Geographical reference systems

spatial-reference notations

• EPSG/SRID: 4326
• EPSG/SRID: 3035
• EPSG/SRID: 32632

proj4 <- '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs' 

Implications

When using real world spatial data obtained from various sources, you will likely encounter different coordinate systems. One of the tasks that you will need to accomplish will be to re-project the data into a common projection system.

Take home message

• Coordinates only do not identify a position on earth and the same position has different values according to the geographical reference system.
• When performing spatial operations (e.g., intersection, union, distance between points from two layers) always make sure the geographical reference systems are the same.

GIS interface

• ArcGIS
• QGIS
• OpenJUMP
• GRASS GIS
• gvsig

GIS Online

• ArcGIS
• CartoDB

GIS Without interface

• GRASS
• R + Rstudio
  • Rshiny
  • Rmarkdown
  • Packages: raster, rgdal, leaflet, mapview, RPostgreSQL,rpostgis, adehabitatHS,adehabitatLT, adehabitatHR,adehabitatMA,sf
• Python
  • Packages: pysal, gdal
• gdal
• Leaflet

GIS and Databases

• SQL Languages
  • PostgreSQL + Postgis extension
  • SQLite
• SQL Graphical User Interfaces
  • pgadmin (GUI: PostgreSQL)
  • TablePlus (GUI: PostgreSQL, MySQL, Mariadb, Mongodb, SQLite,…)

Databases in Ecology

• Euromammals
• UvA-bits
• Movebank

• …

• icarus

Databases in Ecology

• Euromammals
• UvA-bits
• Movebank

• …

• icarus

How does a database look like?

• database graphical user interface: pgadmin, tableplus

GIS and Database

Lets make a database

• data collection
• data processing
• data import
  • create table
  • insert into
• data querying
  • select from where group by order limit
  • join
  • aggregate

Open me

Data collection

• name, family name, gender, date of birth, street, city, country, coordinates
• a list with your hobbies/interests see list of hobbies

degroevejohannes at gmail.com

database structure

Data processing

• formatting tables
  • use csv format instead of excel
  • use a text editor (e.g.sublimetext)
    • regular expressions [0-9].*
    • shortcuts sublime
      • ctrl D = select
      • ctrl F = search
      • ctrl option F = search and replace
      • option right-click

data import

• create hobbies table structure

--DROP TABLE main.hobbies;
CREATE TABLE main.hobbies (
  hobby_id serial NOT NULL PRIMARY KEY,
  hobby_description character varying,
  hobby_type character varying
  );
SELECT * FROM main.hobbies;

data import

• create personal table structure

--DROP TABLE main.personal_data;
CREATE TABLE main.personal_data (
  personal_data_id serial NOT NULL PRIMARY KEY,
  name character varying(20),
  family_name character varying(20),
  email character varying,
  gender character varying(1),
  birth_date date,
  street character varying(30), 
  city character varying(30),
  country character varying(30),
  longitude double precision,
  latitude double precision
  );
SELECT * FROM main.personal_data;

data import

• create personal_hobbies table structure

--DROP TABLE main.personal_hobbies;
CREATE TABLE main.personal_hobbies (
  personal_hobbies_id serial NOT NULL PRIMARY KEY,
  personal_data_id integer REFERENCES main.personal_data(personal_data_id),
  hobby_id integer REFERENCES main.hobbies(hobby_id)
  );
SELECT * FROM main.personal_hobbies;

data import

• insert hobbies in database

INSERT INTO main.hobbies (hobby_description)
VALUES ('playing music'),
       ('listening music'),
       ('dancing'),
       ('cooking'),
       ('eating'),
       ('climbing'),
       ('running'),
       ('hiking'),
       ('swimming');

data import

• example on how to delete data

DELETE FROM main.hobbies; 

data import

• insert hobbies in database

psql -d venice_course -U postgres
\copy main.hobbies(hobby_description, hobby_type) FROM 'hobbies.csv' WITH(FORMAT csv, HEADER true);

data import

• insert personal_data in database

cd Downloads/1_FEM_CRI/VENICE_COURSE/DATABASE/
awk FNR!=1 students_personal_data/*.csv > personal_data.csv

\copy main.personal_data (name,family_name,email,gender,birthday,street,city,country,longitude,latitude) FROM 'personal_data.csv' WITH(FORMAT csv, HEADER true, NULL 'NA');

data import

• create temporary table with the relation between hobbies and personal data

CREATE TABLE temp.personal_hobbies_temp 
(
name character varying,
family_name character varying, 
longitude double precision, 
hobby_description character varying,
personal_data_id integer,
hobby_id integer
);
SELECT * FROM temp.personal_hobbies_temp;

data import

• import personal_hobbies data
  • insert data in a temporary table
  • retrieve hobby_id from the hobbies table
  • retrieve personal_data_id from the personal_data table
  • insert the data from the table temp.personal_hobbies_temp in the table main.personal_hobbies

data import

• insert data in the temporary table

cd Downloads/1_FEM_CRI/VENICE_COURSE/DATABASE/
awk FNR!=1 students_personal_hobbies/*.csv > personal_hobbies.csv

\copy temp.personal_hobbies_temp (name,family_name,longitude,hobby_description) FROM 'personal_hobbies.csv' WITH(FORMAT csv, HEADER true);

data import

• retrieve the hobby_id from the hobbies table

UPDATE temp.personal_hobbies_temp 
SET hobby_id = hobbies.hobby_id
FROM main.hobbies
WHERE 
personal_hobbies_temp.hobby_description = hobbies.hobby_description; 
SELECT * FROM temp.personal_hobbies_temp;

data import

• retrieve the personal_data_id from the personal_data table

UPDATE temp.personal_hobbies_temp 
SET personal_data_id = personal_data.personal_data_id
FROM main.personal_data
WHERE 
personal_hobbies_temp.name = personal_data.name AND 
personal_hobbies_temp.family_name = personal_data.family_name AND 
personal_hobbies_temp.longitude = personal_data.longitude; 
SELECT * FROM temp.personal_hobbies_temp;

data import

• insert the data from the table temp.personal_hobbies_temp in the table main.personal_hobbies

INSERT INTO main.personal_hobbies (personal_data_id, hobby_id)
SELECT personal_data_id, hobby_id FROM temp.personal_hobbies_temp;
SELECT * FROM main.personal_hobbies; 

data querying

Open me

• select

SELECT * FROM main.hobbies;
SELECT * FROM main.personal_data;
SELECT * FROM main.personal_hobbies;

data querying

• select

SELECT * FROM main.personal_data WHERE city = 'Venice';
SELECT * FROM main.personal_data WHERE extract(month from birth_date) = 1;

data querying

• aggregate

SELECT count(*), gender 
FROM main.personal_data 
GROUP BY gender; 

SELECT count(*), extract(year from year_birth) yearx 
FROM main.personal_data 
GROUP BY yearx; 

data querying

• aggregate

-- what is the age of the participants?
SELECT now() - year_birth age 
FROM main.personal_data
ORDER BY age; 

-- what is the average age of the participants?
SELECT avg(now() - year_birth) average_age 
FROM main.personal_data; 
-- what is the average age in years?
SELECT avg(now()::date - birthday)/365 average_age 
FROM main.personal_data; 
-- what is the average age in years by gender?
SELECT avg(now()::date - birthday)/365 average_age, gender 
FROM main.personal_data 
GROUP BY gender; 

data querying

• aggregate

-- How popular is each hobby?
SELECT count(*), hobby_id 
FROM main.personal_hobbies 
GROUP BY hobby_id 
ORDER BY 1; 

data querying

• join two tables

-- TWO ways to write a inner join 
-- Method 1 
SELECT hobby_description, hobby_type 
FROM main.personal_hobbies 
JOIN main.hobbies USING (hobby_id); 
-- Method 2
SELECT hobby_description, hobby_type 
FROM main.personal_hobbies 
WHERE hobbies.hobby_id = personal_hobbies.hobby_id; 

data querying

• join the three tables

-- Method 1 
SELECT name, hobbies_description
FROM main.personal_hobbies 
JOIN main.hobbies USING (hobby_id) 
JOIN main.personal_data USING (personal_data_id);
-- Method 2
SELECT name, hobbies_description 
FROM main.personal_hobbies, main.hobbies, main.personal_data
WHERE hobbies.hobby_id = personal_hobbies.hobby_id AND 
personal_hobbies.personal_data_id = personal_data.personal_data_id; 

data querying

• join and aggregate

-- What is the most common hobby type?
SELECT hobby_type, count(*) 
FROM main.personal_hobbies 
JOIN main.hobbies USING (hobby_id)
GROUP BY hobby_type;
-- how would you extend this to different genders? 

data querying

• join and aggregate

-- What is the most common hobby type?
SELECT hobby_type, count(*) FROM 
  (SELECT DISTINCT hobby_type, personal_data_id
  FROM main.personal_hobbies 
  JOIN main.hobbies USING (hobby_id) 
  JOIN main.personal_data USING (personal_data_id)) subquery
GROUP BY hobby_type;

data querying

• join and aggregate

-- Which hobbies are there?
SELECT hobby_description, count(*)  
  FROM main.personal_hobbies 
  JOIN main.hobbies USING (hobby_id) 
  JOIN main.personal_data USING (personal_data_id)
GROUP BY hobby_description
ORDER BY count(*) DESC;

data querying

• join and aggregate

-- What is the most common hobby?
SELECT hobby_description, count(*)  
  FROM main.personal_hobbies 
  JOIN main.hobbies USING (hobby_id) 
  JOIN main.personal_data USING (personal_data_id)
GROUP BY hobby_description
ORDER BY count(*) DESC 
LIMIT 1;

data querying

• join and aggregate

-- Which hobbies are rare (only occur once)?
SELECT count(*), hobby_description 
FROM main.personal_hobbies 
JOIN main.hobbies using (hobbies_id) 
HAVING count(*) = 1;

data querying

• join and aggregate

-- top three hobbies? 
SELECT hobby_id, cnt FROM (
SELECT *, row_number() over (ORDER BY cnt DESC) FROM (
    SELECT hobby_id,  count(*) cnt
    FROM main.personal_hobbies 
    JOIN main.personal_data USING (personal_data_id) 
    GROUP BY hobby_id) subquery 
    ) subquery
WHERE row_number < 4 ORDER BY  cnt DESC;
    
-- can you extend this to top three per gender? 

eurodeer database structure

Some useful links and programs:

color brewer spatial reference

github sublimetext

This presentation has been made with R and the following R packages:

library(sf)
library(RPostgreSQL)
library(rpostgis)
library(plyr)
library(leaflet)
library(raster)
library(maps)
library(mapdata)
library(maptools)
library(mapview)
library(RgoogleMaps)
library(osmdata)
library(ggplot2)
library(plotly)